{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:46:02.856629Z",
     "iopub.status.busy": "2023-11-07T10:46:02.856438Z",
     "iopub.status.idle": "2023-11-07T10:46:16.048659Z",
     "shell.execute_reply": "2023-11-07T10:46:16.048061Z",
     "shell.execute_reply.started": "2023-11-07T10:46:02.856603Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "stage = 'A'\n",
    "\n",
    "df_train = pd.read_csv('../../../contest/train/GSLD_TR_APS.csv')\n",
    "df_test = pd.read_csv('../../../contest/A/GSLD_TR_APS_A.csv')\n",
    "\n",
    "save_path = '../data'\n",
    "\n",
    "end_date_train = datetime(1996,7,5)\n",
    "end_date_test = datetime(1996,8,4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:46:16.049981Z",
     "iopub.status.busy": "2023-11-07T10:46:16.049778Z",
     "iopub.status.idle": "2023-11-07T10:46:18.754129Z",
     "shell.execute_reply": "2023-11-07T10:46:18.753532Z",
     "shell.execute_reply.started": "2023-11-07T10:46:16.049956Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "abs_head3_list = df_train.APSDABS.value_counts()[:3].index\n",
    "abs_head9_list = df_train.APSDABS.value_counts()[:9].index # 出现大于100000次\n",
    "aps_cd_list = df_train.APSDTRCOD.value_counts().index\n",
    "chl_head17_list = df_train.APSDTRCHL.value_counts()[:17].index # 出现大于1000次"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:46:18.755672Z",
     "iopub.status.busy": "2023-11-07T10:46:18.755458Z",
     "iopub.status.idle": "2023-11-07T10:46:18.772995Z",
     "shell.execute_reply": "2023-11-07T10:46:18.772385Z",
     "shell.execute_reply.started": "2023-11-07T10:46:18.755646Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def prepro(df, end_date):\n",
    "    abs_df = df.copy()\n",
    "    \n",
    "    df['DATE'] = pd.to_datetime(df['DATE'], format='%Y%m%d')\n",
    "    df['APSDTRAMT'] = df.APSDTRAMT.apply(lambda x: round(pow(x/3.12,3),2))\n",
    "    df['abs_amt'] = df['APSDTRAMT'].apply(lambda x: abs(x))\n",
    "    df = df.groupby('CUST_NO').agg(\n",
    "        aps_sum_amt = ('APSDTRAMT', 'sum'),\n",
    "        aps_sum_abs_amt = ('abs_amt', 'sum'),\n",
    "        aps_sum_avg_amt = ('APSDTRAMT', 'mean'),\n",
    "        aps_max_amt = ('APSDTRAMT', 'max'),\n",
    "        aps_min_amt = ('APSDTRAMT', 'min'),\n",
    "        aps_last_diff = ('DATE', 'max'),\n",
    "        aps_code_cnt = ('APSDTRCOD', 'nunique'),\n",
    "        aps_chl_cnt = ('APSDTRCHL', 'nunique'),\n",
    "        aps_abs_cnt = ('APSDABS', 'nunique'),\n",
    "    )\n",
    "    df['aps_last_diff'] = (end_date - df['aps_last_diff']).dt.days\n",
    "    \n",
    "    # 不同交易摘要的最大金额\n",
    "    for i, abs3 in enumerate(abs_head3_list):\n",
    "        tmp_df = abs_df[abs_df['APSDABS']==abs3].groupby('CUST_NO').agg({'APSDTRAMT':'max'}).rename(columns={'APSDTRAMT':'aps_abs'+str(i+1)+'_maxamt'})\n",
    "        df = df.merge(tmp_df, how='left', on='CUST_NO')\n",
    "    \n",
    "    # 不同交易摘要的最小金额\n",
    "    for i, abs3 in enumerate(abs_head3_list):\n",
    "        tmp_df = abs_df[abs_df['APSDABS']==abs3].groupby('CUST_NO').agg({'APSDTRAMT':'min'}).rename(columns={'APSDTRAMT':'aps_abs'+str(i+1)+'_minamt'})\n",
    "        df = df.merge(tmp_df, how='left', on='CUST_NO')\n",
    "\n",
    "    # 不同交易摘要的出现次数\n",
    "    for i, abs9 in enumerate(abs_head9_list):\n",
    "        tmp_df = abs_df[abs_df['APSDABS']==abs9].groupby('CUST_NO').agg({'APSDTRAMT':'count'}).rename(columns={'APSDTRAMT':'aps_abs'+str(i+1)+'_cnt'})\n",
    "        df = df.merge(tmp_df, how='left', on='CUST_NO')\n",
    "\n",
    "    # 交易码是否出现过\n",
    "    for i, cd in enumerate(aps_cd_list):\n",
    "        col_name = 'aps_code'+str(i+1)+'_ind'\n",
    "        tmp_df = abs_df[abs_df['APSDTRCOD']==cd].groupby('CUST_NO').head(1)\n",
    "        tmp_df[col_name] = 1\n",
    "        df = df.merge(tmp_df[['CUST_NO', col_name]], how='left', on='CUST_NO')\n",
    "        df[col_name] = df[col_name].fillna(0)\n",
    "\n",
    "    # 交易渠道是否出现过\n",
    "    for i, chl in enumerate(chl_head17_list):\n",
    "        col_name = 'aps_chl'+str(i+1)+'_ind'\n",
    "        tmp_df = abs_df[abs_df['APSDTRCHL']==chl].groupby('CUST_NO').head(1)\n",
    "        tmp_df[col_name] = 1\n",
    "        df = df.merge(tmp_df[['CUST_NO', col_name]], how='left', on='CUST_NO')\n",
    "        df[col_name] = df[col_name].fillna(0)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:46:18.774062Z",
     "iopub.status.busy": "2023-11-07T10:46:18.773880Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train = prepro(df_train, end_date_train)\n",
    "df_test = prepro(df_test, end_date_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train.to_csv(save_path+'/GSLD_TR_APS.csv', index=False)\n",
    "df_test.to_csv(save_path+'/GSLD_TR_APS_A.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(save_path+'/GSLD_TR_APS_A.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
